package com.sq.util.sql;

import java.lang.reflect.Field;
import java.math.BigDecimal;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.RowMapper;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;

import com.sq.util.BeanUtil;
import com.sq.util.exception.CommonException;

/**
 * Spring的JDBC基类
 * 
 */
public abstract class SpringJdbc {

    protected final Logger log = LoggerFactory.getLogger(getClass());

    @Autowired
    protected JdbcTemplate jdbcTemplate;

    @Autowired
    protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    // 用于返回单行单列的单个数据
    protected <T> T queryFor(Class<T> cls, String sql, Object... args) {
        List<T> list = jdbcTemplate.query(sql, args, getSingleResultRowMapper(cls));
        return getQueryForResult(list);
    }

    // 用于返回单行单列的单个数据
    protected <T> T queryFor(Class<T> cls, String sql, Map<String, Object> paramMap) {
        List<T> list = namedParameterJdbcTemplate.query(sql, paramMap, getSingleResultRowMapper(cls));
        return getQueryForResult(list);
    }

    protected int queryForInt(String sql, Object... args) {
        Integer integer = queryForInteger(sql, args);
        return integer == null ? 0 : integer.intValue();
    }

    protected int queryForInt(String sql, Map<String, Object> paramMap) {
        Integer integer = queryForInteger(sql, paramMap);
        return integer == null ? 0 : integer.intValue();
    }

    protected Integer queryForInteger(String sql, Object... args) {
        return queryFor(Integer.class, sql, args);
    }

    protected Integer queryForInteger(String sql, Map<String, Object> paramMap) {
        return queryFor(Integer.class, sql, paramMap);
    }

    protected BigDecimal queryForBigDecimal(String sql, Object... args) {
        return queryFor(BigDecimal.class, sql, args);
    }

    protected BigDecimal queryForBigDecimal(String sql, Map<String, Object> paramMap) {
        return queryFor(BigDecimal.class, sql, paramMap);
    }

    // 获取返回单列的RowMapper
    protected <T> RowMapper<T> getSingleResultRowMapper(Class<T> cls) {
        return new RowMapper<T>() {
            @SuppressWarnings("unchecked")
            @Override
            public T mapRow(ResultSet rs, int rowNum) throws SQLException {
                Object obj = null;
                if (cls.equals(Integer.class)) {
                    obj = rs.getInt(1);
                } else if (cls.equals(Long.class)) {
                    obj = rs.getLong(1);
                } else if (cls.equals(BigDecimal.class)) {
                    obj = rs.getBigDecimal(1);
                } else if (cls.equals(String.class)) {
                    obj = rs.getString(1);
                } else if (cls.equals(Short.class)) {
                    obj = rs.getShort(1);
                }
                return (T) obj;
            }
        };
    }

    protected int batchUpdate(String sql, List<?> list) {
        return batchUpdate(sql, createBatchSqlParameterSource(list));
    }

    // 用于返回单行多列的情况
    @SuppressWarnings("unchecked")
    protected <T> T queryForObject(Class<?> cls, String sql, Object... args) {
        return (T) DataAccessUtils.singleResult(jdbcTemplate.query(sql, getRowMapper(cls), args));
    }

    /**
     * 用于返回多行多列的情况
     */
    protected <T> List<T> queryForList(Class<T> cls, String sql, Object... args) {
        return jdbcTemplate.query(sql, getRowMapper(cls), args);
    }

    /**
     * 用于返回多行多列的情况
     */
    protected <T> List<T> queryForList(Class<?> cls, String sql, Map<String, ?> paramMap) {
        return namedParameterJdbcTemplate.query(sql, paramMap, getRowMapper(cls));
    }

    protected int update(String sql, Object... args) {
        return jdbcTemplate.update(sql, args);
    }

    protected int update(String sql, Map<String, Object> paramMap) {
        return namedParameterJdbcTemplate.update(sql, paramMap);
    }

    protected int updateObject(String sql, Object obj) {
        BeanPropertySqlParameterSource beanPropertySqlParameterSource = new BeanPropertySqlParameterSource(obj);
        return namedParameterJdbcTemplate.update(sql, beanPropertySqlParameterSource);
    }

    protected <T> List<T> query(String sql, RowMapper<T> rowMapper, Object... args) {
        return jdbcTemplate.query(sql, rowMapper, args);
    }

    protected <T> List<T> query(String sql, Map<String, Object> paramMap, RowMapper<T> rowMapper) {
        return namedParameterJdbcTemplate.query(sql, paramMap, rowMapper);
    }

    @SuppressWarnings({ "unchecked", "rawtypes" })
    protected <T> BeanPropertyRowMapper<T> getRowMapper(Class<?> cls) {
        return new BeanPropertyRowMapper(cls);
    }

    /**
     * 用于主键是自增的数据插入,主键名为"id"
     */
    protected int insertWithAutoIncrement(Object entity, String insertSql) {
        return insertWithAutoIncrement(entity, insertSql, "id");
    }

    /**
     * 用于主键是自增的数据插入
     * 
     * @param idName
     *            自增主键对应的类属性
     */
    protected int insertWithAutoIncrement(Object entity, String insertSql, String idName) {
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int affectedRows = namedParameterJdbcTemplate.update(insertSql, new BeanPropertySqlParameterSource(entity),
                keyHolder);
        if (affectedRows > 0) {
            setIdentifierProperty(entity, keyHolder.getKey().longValue(), idName);
        }
        return affectedRows;
    }

    /**
     * 用于主键是自己生成的数据插入,自增主键用这个会获取不到自增后的id
     */
    protected int insertWithAssigned(Object entity, String insertSql) {
        return namedParameterJdbcTemplate.update(insertSql, new BeanPropertySqlParameterSource(entity));
    }

    /**
     * 设置实体的主键值
     */
    private void setIdentifierProperty(Object entity, Object id, String idName) {
        try {
            Field field = BeanUtil.getFieldWithExtend(entity.getClass(), idName);
            BeanUtil.setProperty(entity, field, id);
        } catch (Exception e) {
            throw new CommonException(e, "不能设置类[{}]的属性[{}]为[{}].", entity.getClass(), idName, id);
        }
    }

    private <T> T getQueryForResult(List<T> list) {
        if (list.size() == 0) {
            return null;
        }
        CommonException.isTrue(list.size() == 1, "返回结果行数不为1");
        return list.get(0);
    }

    private SqlParameterSource[] createBatchSqlParameterSource(List<?> list) {
        BeanPropertySqlParameterSource[] batch = new BeanPropertySqlParameterSource[list.size()];
        int i = 0;
        for (Object object : list) {
            batch[i] = new BeanPropertySqlParameterSource(object);
            i++;
        }
        return batch;
    }

    private int batchUpdate(String sql, SqlParameterSource[] params) {
        int[] arr = namedParameterJdbcTemplate.batchUpdate(sql, params);
        int num = 0;
        for (int i : arr) {
            num += i;
        }
        return num;
    }
}